﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;
using System.IO;
using System.Xml;

namespace SimpleLibrary
{
   
    public class DataAccess
    {
        // Execute Task
        public void ExecuteMyTask(SQLTask task,LocalData localdata)
        {
            bool bTxAbort = false;
            List<TempStatistics> txStat = new List<TempStatistics>();

            using (TransactionScope ts = new TransactionScope(task.txOption))
            {
                using (SqlConnection conn = new SqlConnection(task.connectionString))
                {
                    conn.Open();
                    foreach (SqlCommand comm in task.commands)
                    {
                        ParameterValueBinding(comm, localdata);
                        comm.Connection = conn;
                        try
                        {
                            DateTime dtStart = DateTime.Now;
                            comm.ExecuteNonQuery();
                            TimeSpan timespan = DateTime.Now - dtStart;
                            // record statistic immediately when there's no transaction
                            if (task.txOption == TransactionScopeOption.Suppress)
                            {
                                QueryStatistics statistics = new QueryStatistics();
                                statistics.Insert(task.name, comm.CommandText, timespan.TotalMilliseconds);
                            }
                            else
                            {
                                TempStatistics tempstat = new TempStatistics() { strTaskName = task.name, strCommandText = comm.CommandText, iExecutionTime = timespan.TotalMilliseconds };
                                txStat.Add(tempstat);
                            }
                            Console.Write(".");
                        }
                        catch (Exception ex)
                        {
                            Console.Write("x");
                            ExceptionStatistics statistics = new ExceptionStatistics();
                            statistics.Insert(task.name, comm.CommandText, ex, comm);

                            // if the task support transaction and there's an exception 
                            // then transaction need to be aborted.
                            if (task.txOption == TransactionScopeOption.Required)
                            {
                                bTxAbort = true;
                                break;
                            }
                            
                        }
                    }
                }

                // only call the complete method when the task completed without exception
                if ((task.txOption == TransactionScopeOption.Required & bTxAbort == false))
                {
                    ts.Complete();
                    // Update QueryStatistics
                    foreach (TempStatistics stat in txStat)
                    {
                        QueryStatistics statistics = new QueryStatistics();
                        statistics.Insert(stat.strTaskName, stat.strCommandText, stat.iExecutionTime);
                    }
                }
                // or when the task didn't support transaction 
                else if (task.txOption == TransactionScopeOption.Suppress)
                {
                    ts.Complete();
                }
                    
            }
        }

        // Binding Parameter Values
        void ParameterValueBinding(SqlCommand command, LocalData localdata)
        {
            int iCount = 0; 
            int iRowNum = 0;
            foreach (SqlParameter parm in command.Parameters)
            {
                // Findout which datatable to access
                string strValues = (string)SqlLoadGen.mySource[command.CommandText + parm.ParameterName];
                string strTableName = strValues.Split('|')[0];
                string strKeyField = strValues.Split('|')[1];

                // access row number within datatable
                // If there's no datatable to associated with, then we don't need pointer
                if (iCount == 0 && string.IsNullOrEmpty(strTableName) == false)
                {
                    iRowNum = Convert.ToInt32(localdata.myPointers[strTableName]);
                    localdata.SetPointer(strTableName, (iRowNum + SqlLoadGen.numberOfWorker));
                    iCount++;
                }

                // special handling for special field
                if (strKeyField.ToLower() == "autogen")
                {
                    parm.Value = KeyGenerator.getKey(command.CommandText + parm.ParameterName.Replace("@", ""));
                }
                else if (parm.SqlDbType == SqlDbType.Image)
                {
                    string strColumValue = Convert.ToString(SqlLoadGen.dsCSVs.Tables[strTableName].Rows[iRowNum][strKeyField]);
                    parm.Value = GetPhoto(strColumValue);
                }
                else if (parm.SqlDbType == SqlDbType.DateTime)
                {
                    string strColumValue = Convert.ToString(SqlLoadGen.dsCSVs.Tables[strTableName].Rows[iRowNum][strKeyField]);
                    DateTime dt = Convert.ToDateTime(strColumValue);
                    parm.Value = dt;
                }
                else if (parm.SqlDbType == SqlDbType.Bit)
                {
                    string strColumValue = Convert.ToString(SqlLoadGen.dsCSVs.Tables[strTableName].Rows[iRowNum][strKeyField]);
                    bool bValue = Convert.ToBoolean(strColumValue.ToLower());
                    parm.Value = bValue;
                }
                else if (parm.SqlDbType == SqlDbType.Money)
                {
                    string strColumValue = Convert.ToString(SqlLoadGen.dsCSVs.Tables[strTableName].Rows[iRowNum][strKeyField]);
                    decimal dValue = Convert.ToDecimal(strColumValue);
                    parm.Value = dValue;
                }
                else if (parm.SqlDbType == SqlDbType.Xml)
                {
                    string strColumValue = Convert.ToString(SqlLoadGen.dsCSVs.Tables[strTableName].Rows[iRowNum][strKeyField]);
                    XmlDocument doc = new XmlDocument();
                    doc.Load(strColumValue);
                    parm.Value = doc.OuterXml;
                }
                else
                {
                    parm.Value = SqlLoadGen.dsCSVs.Tables[strTableName].Rows[iRowNum][strKeyField];
                }
            }
        }

        // Get Image bytes
        public byte[] GetPhoto(string filePath)
        {
            FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
            BinaryReader br = new BinaryReader(fs);

            byte[] photo = null;
            try
            {
                photo = br.ReadBytes((int)fs.Length);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                br.Close();
                fs.Close();
            }
            return photo;
        }

    }
}
